﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Devart.Data;
using Devart.Data.SQLite;

namespace Qero.ChartPoint
{
    class DataClass
    {
        
        public static string FLD_Uid(CPTable dataTbl) { return string.Format("{0}Uid", dataTbl); }
        public static string FLD_Sub(CPTable dataTbl) { return string.Format("{0}Sub", dataTbl); }
        public static string FLD_Name(CPTable dataTbl) { return string.Format("{0}Name", dataTbl); }
        public static string FLD_Imag(CPTable dataTbl) { return string.Format("{0}Imag", dataTbl); }
        public static string FLD_Type(CPTable dataTbl) { return string.Format("{0}Type", dataTbl); }
        public static string FLD_Enum(CPTable dataTbl) { return string.Format("{0}Enum", dataTbl); }
        public static string FLD_File(CPTable dataTbl) { return string.Format("{0}Files", dataTbl); }
        
    }

    public class SQLiteClass
    {
        /// <summary> Empty string
        /// </summary>
        public static string QS_ESp { get { return string.Empty; } }
        public static string QS_Whr { get { return "WHERE "; } }
        public static string QS_And { get { return "AND "; } }

        private string _dbpath = string.Empty;
        public string dbPath
        {
            get { return _dbpath; }
            private set { _dbpath = value; }
        }

        private string _errmsg = string.Empty;
        public string errorMessage
        {
            get { return _errmsg; }
            private set { _errmsg = value; }
        }

        public string connString
        {
            get { return string.Format(@"Data Source={0}", dbPath); }
        }

        public SQLiteClass(string correctPath)
        {
            if (!string.IsNullOrEmpty(correctPath))
                this._dbpath = correctPath;
        }

        public static SQLiteType QDType2DBType(QDType convertType)
        {
            if (convertType == QDType.GUID) return SQLiteType.Guid;
            else if (convertType == QDType.BLOB) return SQLiteType.Blob;
            else if (convertType == QDType.BOOL) return SQLiteType.Int16;
            else return SQLiteType.Text;
        }

        private static string QueryFieldsSet(List<DataQuark> selValues, string separator, bool? isReq)
        {
            if (selValues.Count == 0) return QS_ESp;

            return string.Join(separator, (from t in selValues select t.Attrib2Query(isReq)));
        }

        public List<DataQuarkLine> TableSelect(DataQuarkLine tblPreset)
        {
            return TableSelect(tblPreset, "SELECT * FROM {0}{1}{2}");
        }

        public List<DataQuarkLine> TableSelect(DataQuarkLine tblPreset, string queryFormat)
        {
            List<DataQuark> selValues = tblPreset.SelValues();

            string selectQuery = string.Format(queryFormat, tblPreset.tableCaption, (selValues.Count > 0) ? QS_Whr : QS_ESp, QueryFieldsSet(selValues, QS_And, null));

            return ExecuteQuery(selectQuery, tblPreset, true);
        }

        public int TableSet(DataQuarkLine tblPreset, bool needUpdate)
        {
            List<DataQuark> setValues = tblPreset.SetValues();

            if ((setValues == null) || (setValues.Count < 1)) return -1;

            return (needUpdate) ? TableUpdate(tblPreset) : TableInsert(tblPreset);
        }

        private int TableInsert(DataQuarkLine tblPreset)
        {
            return TableInsert(tblPreset, "INSERT INTO {0}({1}) VALUES ({2})");
        }

        private int TableInsert(DataQuarkLine tblPreset, string queryFormat)
        {
            List<DataQuark> setValues = tblPreset.SetValues();

            string insertQuery = string.Format(queryFormat, tblPreset.tableCaption, QueryFieldsSet(setValues, ",", false), QueryFieldsSet(setValues, ",", true));

            return ExecuteQuery(insertQuery, tblPreset, false).Count;
        }

        private int TableUpdate(DataQuarkLine tblPreset)
        {
            return TableUpdate(tblPreset, "UPDATE {0} SET {1} {2}{3}");
        }

        private int TableUpdate(DataQuarkLine tblPreset, string queryFormat)
        {
            List<DataQuark> setValues = tblPreset.SetValues(), selValues = tblPreset.SelValues();

            string updateQuery = string.Format(queryFormat, tblPreset.tableCaption, QueryFieldsSet(setValues, ",", null), (selValues.Count > 0) ? QS_Whr : QS_ESp, QueryFieldsSet(selValues, QS_And, null));

            return ExecuteQuery(updateQuery, tblPreset, false).Count;
        }

        private List<DataQuarkLine> ExecuteQuery(string queryString, DataQuarkLine querySet, bool returnSet)
        {
            List<DataQuarkLine> queryResult = new List<DataQuarkLine>();

            SQLiteConnection sqConn = null;

            try
            {
                using (sqConn = new SQLiteConnection(connString))
                {
                    sqConn.Open();

                    using (SQLiteCommand sqComm = new SQLiteCommand(queryString, sqConn))
                    {
                        foreach (DataQuark queryParam in querySet.LineSet)
                            sqComm.Parameters.Add(queryParam.attribName, QDType2DBType(queryParam.dataType)).Value = queryParam.dataValue;

                        if (returnSet)
                        {
                            SQLiteDataReader sqReader = sqComm.ExecuteReader();

                            if (sqReader != null)
                            {
                                while (sqReader.Read())
                                {
                                    DataQuarkLine outputDataLine = new DataQuarkLine();

                                    foreach (DataQuark setAttrib in querySet.LineSet)
                                    {
                                        int rdOrd = sqReader.GetOrdinal(setAttrib.attribName);
                                        outputDataLine.LineSet.Add(new DataQuark(setAttrib.attribName, setAttrib.dataType, (rdOrd >= 0) ? sqReader.GetValue(setAttrib.attribName) : new object()));
                                    }

                                    queryResult.Add(outputDataLine);
                                }

                                sqReader.Close();
                                sqReader.Dispose();
                            }
                        }
                        else
                        {
                            int outputCount = sqComm.ExecuteNonQuery();

                            for (int step = 0; step < outputCount; step++)
                                queryResult.Add(new DataQuarkLine());
                        }
                    }
                }
            }
            catch (Exception ex) { errorMessage = ex.Message; }
            finally
            {
                if (sqConn != null)
                {
                    if (sqConn.State != ConnectionState.Closed) sqConn.Close();
                    sqConn.Dispose();
                }
            }

            return queryResult;
        }

    }
}